package com.yjyw.tiexie.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.yjyw.tiexie.bean.UserA;
import com.yjyw.tiexie.bean.UserB;
import com.yjyw.tiexie.bean.UserC;
import com.yjyw.tiexie.util.LogUtil;

import java.util.ArrayList;
import java.util.List;

public class UserBDBHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "userB.db";
    private static final String TABLE_NAME = "tableB";
    private static final String TABLE_A_NAME = "tableA";
    private static final String TABLE_C_NAME = "tableC";

    private static final int DB_VERSION = 1;
    private static UserBDBHelper mHelper = null;
    private SQLiteDatabase mRDB = null;
    private SQLiteDatabase mWDB = null;

    private UserBDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    //利用单例模式获取数据库帮助器的唯一实例
    public static UserBDBHelper getInstance(Context context) {
        if (mHelper == null) {
            mHelper = new UserBDBHelper(context);
        }
        return mHelper;
    }

    // 打开数据库的读连接
    public SQLiteDatabase openReadLink() {
        if (mWDB == null || !mWDB.isOpen()) {
            mWDB = mHelper.getReadableDatabase();
        }
        return mWDB;
    }

    // 打开数据库的写连接
    public SQLiteDatabase openWriteLink() {
        if (mRDB == null || !mRDB.isOpen()) {
            mRDB = mHelper.getWritableDatabase();
        }
        return mRDB;
    }

    //关闭数据库连接
    public void closeLink() {
        if (mRDB != null && mRDB.isOpen()) {
            mRDB.close();
            mRDB = null;
        }

        if (mWDB != null && mWDB.isOpen()) {
            mWDB.close();
            mWDB = null;
        }
    }

    // 创建数据库，执行建表语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建A表
        //"xjStart": "2023-12-07 13:41:08",//巡检开始时间
        //	"xjUser": "01dc38fdf63811edbfd10050568b0d88",//巡检用户
        //	"xjEndTime": "2023-12-07 13:45:08",//结束时间
        //	"xjCounts": "100",//总数
        //	"xjNums": "99"//被扫到的总数
        //	"ylNum": "1",//没扫到的
        //	"remark": "000000521320230061200060"//备注，逗号分割上传未扫到的
        //
        //	“bdIdstatus” ：“0” //巡检id更新状态（0/1） 初始化为0
        //	“bdId” ：“0” //巡检id   自己生成
        //	“bdStatus” ：“0” //巡检状态（0/1/2） 0待开始 1待结束，2已结束
        String sqlA = "CREATE TABLE IF NOT EXISTS " + TABLE_A_NAME + " (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                "xjStart VARCHAR NOT NULL," +
                "xjUser VARCHAR NOT NULL," +
                "xjEndTime VARCHAR NOT NULL," +
                "xjCounts INTEGER NOT NULL," +
                "xjNums INTEGER NOT NULL," +
                "ylNum INTEGER NOT NULL," +
                "remark VARCHAR NOT NULL," +
                "bdIdstatus INTEGER NOT NULL," +
                "bdStatus INTEGER NOT NULL," +
                "bdId VARCHAR NOT NULL);";
        db.execSQL(sqlA);

        //B表
        // "txBh": "合机01 机10 02",
        //	"smTime": "2023-06-07 13:52:27",//扫码时间
        //	"gps": "1",//默认
        //	"x": "1",//默认
        //	"y": "1",//默认
        //	"xjUser": "01dc38fdf63811edbfd10050568b0d88",//用户
        //	"smType": "1005",
        //	"xjId": "d0e5200fe47b4371b3e3008ac63ce886",//巡检id 我自定义的对应AbdId
        //	"cardNo": "000000521320230082700019",//
        //	"status": "良好"//状态默认良好
        //
        //	xjIdStatus//巡检id更新状态 默认为0 （0/1）
        //	xjTjStatus//默认为0  提交记录状态（0/1）
        //	xjScanStatus//默认为0   被扫码状态（0/1）
        //	xjEndSttatus//默认为0  巡检结束状态（0/1）
        String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                "txBh VARCHAR NOT NULL," +
                "smTime VARCHAR NOT NULL," +
                "gps VARCHAR NOT NULL," +
                "x VARCHAR NOT NULL," +
                "y VARCHAR NOT NULL," +
                "xjUser VARCHAR NOT NULL," +
                "smType VARCHAR NOT NULL," +
                "xjId VARCHAR NOT NULL," +
                "cardNo VARCHAR NOT NULL," +
                "status VARCHAR NOT NULL," +
                "xjIdStatus INTEGER NOT NULL," +
                "xjScanStatus INTEGER NOT NULL," +
                "xjTjStatus INTEGER NOT NULL," +
                "xjEndSttatus INTEGER NOT NULL);";
        db.execSQL(sql);
        //TABLE_C_NAME
        // public String fileName;
        //    public int bdIdstatus;
        //    public int upFileStatus;
        //    public int upFileRecordStatus;
        //    public String serverFilePath;
        //    public String xjUser;
        //
        //    public String filePath;
        //
        //    public String bdId;//巡检id
        String sqlC = "CREATE TABLE IF NOT EXISTS " + TABLE_C_NAME + " (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                "fileName VARCHAR NOT NULL," +
                "bdIdstatus INTEGER NOT NULL," +
                "upFileStatus INTEGER NOT NULL," +
                "upFileRecordStatus INTEGER NOT NULL," +
                "serverFilePath VARCHAR NOT NULL," +
                "xjUser VARCHAR NOT NULL," +
                "filePath VARCHAR NOT NULL," +
                "bdId VARCHAR NOT NULL);";
        db.execSQL(sqlC);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public long insert(UserB user) {
        ContentValues values = new ContentValues();
        values.put("txBh", user.txBh);
        values.put("smTime", user.smTime);
        values.put("gps", user.gps);
        values.put("x", user.x);
        values.put("y", user.y);
        values.put("xjUser", user.xjUser);
        values.put("smType", user.smType);
        values.put("xjId", user.xjId);
        values.put("cardNo", user.cardNo);
        values.put("status", user.status);
        values.put("xjIdStatus", user.xjIdStatus);
        values.put("xjScanStatus", user.xjScanStatus);
        values.put("xjTjStatus", user.xjTjStatus);
        values.put("xjEndSttatus", user.xjEndSttatus);
        return mWDB.insert(TABLE_NAME, null, values);
    }


    public long insertA(UserA user) {
        ContentValues values = new ContentValues();
        values.put("xjStart", user.xjStart);
        values.put("xjUser", user.xjUser);
        values.put("xjEndTime", user.xjEndTime);
        values.put("xjCounts", user.xjCounts);
        values.put("xjNums", user.xjNums);
        values.put("ylNum", user.ylNum);
        values.put("remark", user.remark);
        values.put("bdIdstatus", user.bdIdstatus);
        values.put("bdStatus", user.bdStatus);
        values.put("bdId", user.bdId);
        return mWDB.insert(TABLE_A_NAME, null, values);
    }
    public long insertC(UserC user) {
        ContentValues values = new ContentValues();
        values.put("fileName", user.fileName);
        values.put("bdIdstatus", user.bdIdstatus);
        values.put("upFileStatus", user.upFileStatus);
        values.put("upFileRecordStatus", user.upFileRecordStatus);
        values.put("serverFilePath", user.serverFilePath);
        values.put("xjUser", user.xjUser);
        values.put("filePath", user.filePath);
        values.put("bdId", user.bdId);
        return mWDB.insert(TABLE_C_NAME, null, values);
    }


    public long deleteByName(String xjUser) {
        // 删除所有
//        mWDB.delete(TABLE_NAME,"1=1", null);
        try {
            return mWDB.delete(TABLE_NAME, "xjUser=?", new String[]{xjUser});
        } catch (Exception e) {
            return 0;
        }

    }
    public long deleteByNameA(String xjUser) {
        try {
            return mWDB.delete(TABLE_A_NAME, "xjUser=?", new String[]{xjUser});
        } catch (Exception e) {
            return 0;
        }

    }
    public long deleteByNameC(String xjUser) {
        try {
            return mWDB.delete(TABLE_C_NAME, "xjUser=?", new String[]{xjUser});
        } catch (Exception e) {
            return 0;
        }

    }

    public long update(UserB user) {
        ContentValues values = new ContentValues();
        values.put("txBh", user.txBh);
        values.put("smTime", user.smTime);
        values.put("gps", user.gps);
        values.put("x", user.x);
        values.put("y", user.y);

        values.put("xjUser", user.xjUser);
        values.put("smType", user.smType);
        values.put("xjId", user.xjId);
        values.put("cardNo", user.cardNo);
        values.put("status", user.status);
        values.put("xjIdStatus", user.xjIdStatus);
        values.put("xjScanStatus", user.xjScanStatus);
        values.put("xjTjStatus", user.xjTjStatus);
        values.put("xjEndSttatus", user.xjEndSttatus);
        return mWDB.update(TABLE_NAME, values, "cardNo=?", new String[]{user.cardNo});
    }


    public long updateA(UserA user) {
        ContentValues values = new ContentValues();
        values.put("xjStart", user.xjStart);
        values.put("xjUser", user.xjUser);
        values.put("xjEndTime", user.xjEndTime);
        values.put("xjCounts", user.xjCounts);
        values.put("xjNums", user.xjNums);
        values.put("ylNum", user.ylNum);
        values.put("remark", user.remark);
        values.put("bdIdstatus", user.bdIdstatus);
        values.put("bdStatus", user.bdStatus);
        values.put("bdId", user.bdId);
        return mWDB.update(TABLE_A_NAME, values, "xjUser=?", new String[]{user.xjUser});
    }
    public long updateC(UserC user) {
        ContentValues values = new ContentValues();
        values.put("fileName", user.fileName);
        values.put("bdIdstatus", user.bdIdstatus);
        values.put("upFileStatus", user.upFileStatus);
        values.put("upFileRecordStatus", user.upFileRecordStatus);
        values.put("serverFilePath", user.serverFilePath);
        values.put("xjUser", user.xjUser);
        values.put("filePath", user.filePath);
        values.put("bdId", user.bdId);
        return mWDB.update(TABLE_C_NAME, values, "xjUser=?", new String[]{user.xjUser});
    }

    public List<UserB> quertByName(String xjUser) {
        List<UserB> list = new ArrayList<>();
        // 执行记录查询动作，该语句返回结果集的游标
        Cursor cursor = mRDB.query(TABLE_NAME, null, "xjUser=?", new String[]{xjUser}, null, null, null);
        // 循环取出游标指向的每条记录
        while (cursor.moveToNext()) {
            UserB user = new UserB();
            user.id = cursor.getInt(0);
            user.txBh = cursor.getString(1);
            user.smTime = cursor.getString(2);
            user.gps = cursor.getString(3);
            user.x = cursor.getString(4);
            user.y = cursor.getString(5);
            user.xjUser = cursor.getString(6);
            user.smType = cursor.getString(7);
            user.xjId = cursor.getString(8);
            user.cardNo = cursor.getString(9);
            user.status = cursor.getString(10);
            user.xjIdStatus = cursor.getInt(11);
            user.xjScanStatus = cursor.getInt(12);
            user.xjTjStatus = cursor.getInt(13);
            user.xjEndSttatus = cursor.getInt(14);
            // SQLite没有布尔类型，用0表示false，用1表示true
//            user.married = (cursor.getInt(5) == 0) ? false : true;
            list.add(user);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    public List<UserA> quertByNameA(String xjUser) {
        List<UserA> list = new ArrayList<>();
        // 执行记录查询动作，该语句返回结果集的游标
        Cursor cursor = mRDB.query(TABLE_A_NAME, null, "xjUser=?", new String[]{xjUser}, null, null, null);
        // 循环取出游标指向的每条记录
        while (cursor.moveToNext()) {
            UserA user = new UserA();
            user.id = cursor.getInt(0);
            user.xjStart = cursor.getString(1);
            user.xjUser = cursor.getString(2);
            user.xjEndTime = cursor.getString(3);
            user.xjCounts = cursor.getInt(4);
            user.xjNums = cursor.getInt(5);
            user.ylNum = cursor.getInt(6);
            user.remark = cursor.getString(7);
            user.bdIdstatus = cursor.getInt(8);
            user.bdStatus = cursor.getInt(9);
            user.bdId = cursor.getString(10);
            list.add(user);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }



    public List<UserC> quertByNameC(String xjUser) {
        List<UserC> list = new ArrayList<>();
        // 执行记录查询动作，该语句返回结果集的游标
        Cursor cursor = mRDB.query(TABLE_A_NAME, null, "xjUser=?", new String[]{xjUser}, null, null, null);
        // 循环取出游标指向的每条记录
        //   // public String fileName;
        //        //    public int bdIdstatus;
        //        //    public int upFileStatus;
        //        //    public int upFileRecordStatus;
        //        //    public String serverFilePath;
        //        //    public String xjUser;
        //        //
        //        //    public String filePath;
        //        //
        //        //    public String bdId;//巡检id
        while (cursor.moveToNext()) {
            UserC user = new UserC();
            user.id = cursor.getInt(0);
            user.fileName = cursor.getString(1);
            user.bdIdstatus = cursor.getInt(2);
            user.upFileStatus = cursor.getInt(3);
            user.upFileRecordStatus = cursor.getInt(4);
            user.serverFilePath = cursor.getString(5);
            user.xjUser = cursor.getString(6);
            user.filePath = cursor.getString(7);
            user.bdId = cursor.getString(8);
            list.add(user);
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    public List<UserB> quertByStatus() {
        String querySql = "select * from tableB where xjTjStatus=0 AND xjIdStatus=1";

        List<UserB> list = new ArrayList<>();
        // 执行记录查询动作，该语句返回结果集的游标
        //查询结果
        Cursor cursor = mRDB.rawQuery(querySql, null);
        // 循环取出游标指向的每条记录
        while (cursor.moveToNext()) {
            UserB user = new UserB();
            user.id = cursor.getInt(0);
            user.txBh = cursor.getString(1);
            user.smTime = cursor.getString(2);
            user.gps = cursor.getString(3);
            user.x = cursor.getString(4);
            user.y = cursor.getString(5);
            user.xjUser = cursor.getString(6);
            user.smType = cursor.getString(7);
            user.xjId = cursor.getString(8);
            user.cardNo = cursor.getString(9);
            user.status = cursor.getString(10);
            user.xjIdStatus = cursor.getInt(11);
            user.xjScanStatus = cursor.getInt(12);
            user.xjTjStatus = cursor.getInt(13);
            user.xjEndSttatus = cursor.getInt(14);
            // SQLite没有布尔类型，用0表示false，用1表示true
//            user.married = (cursor.getInt(5) == 0) ? false : true;
            list.add(user);
        }
        return list;
    }
}
